/****** Object:  Function dbo.fn_asi_GetCompanySort  ******/
-- =============================================
-- This function takes CompanyName and calculates CompanySort
-- depending on the defined rules.
-- Default rules are:
-- - Remove foreign characters
-- - Uppercase the string
-- - Remove leading words ("The ", "Le ", "La ", "L'")
-- =============================================
create function dbo.fn_asi_GetCompanySort(@CompanyName varchar(80))
      returns varchar(30)
  AS
  BEGIN
      
      DECLARE @Sort varchar(80),
         @CompanySort varchar(30),
          @PrefixList varchar(2000),
          @SimpleSort varchar(80),
          @FirstWord varchar(80),
             @EndPosSpace int,
          @EndPosQuote int,
          @Pos int,
          @EndPos int
      -- get the prefix list
      SELECT @PrefixList = CASE WHEN Len(ShortValue) > 0 THEN ShortValue
                           ELSE LongValue END
          FROM System_Params
          WHERE ParameterName = 'System_Control.CompanySortPrefixes'
     IF @PrefixList IS NULL
         SET @PrefixList = ''
      IF LEN(@PrefixList) = 0
          SET @PrefixList = ' THE '
      ELSE
          SET @PrefixList = UPPER(@PrefixList)
      IF RIGHT(@PrefixList,1) <> ' '
          SET @PrefixList = @PrefixList + ' '
      IF LEFT(@PrefixList,1) <> ' '
          SET @PrefixList = ' ' + @PrefixList
      -- DEBUG SELECT 'PrefixList=' + @PrefixList
      -- Prepare the sort
      -- Strip out foreign characters and other chars not needed
      SET @SimpleSort = RTRIM(LTRIM(UPPER(dbo.fn_asi_convert_foreign_string(@CompanyName))))
      SET @SimpleSort = REPLACE(@SimpleSort,CHAR(10),' ')
      SET @SimpleSort = REPLACE(@SimpleSort,CHAR(13),' ')
      SET @Sort = @SimpleSort
      -- Get the first word or prefix from the company name
      SET @EndPosSpace = CHARINDEX(' ',@Sort)
      IF @EndPosSpace = 0
          SET @EndPosSpace = LEN(@Sort)
      SET @EndPosQuote = CHARINDEX('''',@Sort)
      IF @EndPosQuote = 0
          SET @EndPosQuote = LEN(@Sort)
      -- if it is a quote, we need to keep the quote in the string
      IF @EndPosSpace < @EndPosQuote
          SET @EndPos = @EndPosSpace-1
      ELSE
          SET @EndPos = @EndPosQuote
      -- now see if the first word or prefix is in the prefix list
      SET @FirstWord = SUBSTRING(@Sort,1,@EndPos)    
      SET @Pos = CHARINDEX(' ' + @FirstWord + ' ',@PrefixList)
      IF CHARINDEX(' ' + @FirstWord + ' ',@PrefixList)>0
      BEGIN
          -- The Company Sort starts with one of the words in the list
          SET @Sort = LTRIM(SUBSTRING(@Sort,@EndPos+1,LEN(@Sort)))
      END
      -- if after removing the unwanted words we are left with nothing, keep
      -- the original
      IF @Sort=''
          SET @Sort = @SimpleSort
      SET @CompanySort = LEFT(LTRIM(@Sort), 30)
      return @CompanySort
END

GO
GRANT EXECUTE ON  [dbo].[fn_asi_GetCompanySort] TO [IMIS]
GO